export const getTextItemPaged = (db, page, pageSize, keyWord) => {
  const offset = (page - 1) * pageSize;

  let sql = `SELECT * FROM textItem WHERE isDeleted = 0 LIMIT ? OFFSET ?`;
  let params = [pageSize, offset];

  if (keyWord) {
    sql = "SELECT * FROM textItem WHERE isDeleted = 0 and (title like ? or description like ?) LIMIT ? OFFSET ?";
    params = [`%${keyWord}%`, `%${keyWord}%`, pageSize, offset];
  }

  return db.prepare(sql).all(...params);
};

export const getAllItems = (db) => {
  return db.prepare("SELECT * FROM textItem where isDeleted = 0").all();
};

export const createTextItem = (db, title, description) => {
  const result = db.prepare(
    "INSERT INTO textItem (title, description, isDeleted) VALUES (?, ?, 0)"
  ).run(title, description);
  return { lastID: result.lastInsertRowid };
};

export const deleteTextItem = (db, id) => {
  return db.prepare("DELETE FROM textItem WHERE id = ?").run(id);
};

export const softDeleteTextItem = (db, id) => {
  return db.prepare("UPDATE textItem SET isDeleted = 1 WHERE id = ?").run(id);
};

export const updateTextItem = (db, id, title, description) => {
  return db.prepare(
    "UPDATE textItem SET title = ?, description = ? WHERE id = ?"
  ).run(title, description, id);
};

export const getTextItemById = (db, id) => {
  return db.prepare("SELECT * FROM textItem WHERE id = ?").get(id);
};

export const getTextItemCount = (db) => {
  return db.prepare("SELECT COUNT(*) as total FROM textItem WHERE isDeleted = 0").get();
};
